-- STANDARD SCRIPT
-- POPULATE insurance1_sent_detail_t TABLE 
--
--


------------------------------------------------
-- FUNCTION TO INSERT NEW ROWS INTO ACCOUNT TABLE


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION insurance1_sent_detail_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
BEGIN

SELECT INTO v_batch_run_date our_run_date from batch_insurance1_t limit 1;
SELECT INTO v_cust_id cust_id from batch_insurance1_t limit 1;


--PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,8,'START INSERT IN insurance1_sent_detail_t');




-- STEP 1 FOR RE-RUN DELETE RECORD IN insurance1_sent_detail_t WITH SAME cust_id AND our_run_date

DELETE FROM insurance1_sent_detail_t WHERE cust_id = v_cust_id AND our_run_date = v_batch_run_date;




-- STEP 2 INSERT RECORDS INTO insurance1_sent_detail_t    

RAISE NOTICE 'START INSERT INTO insurance1_sent_detail_t TABLE';

  
  INSERT INTO insurance1_sent_detail_t
  SELECT
    cust_id               ,
    our_run_date          ,
    carrier_code          ,
    count(*) as emdeon_out            

  FROM

    (
     SELECT
        account.cust_id as cust_id,
        account.our_run_date as our_run_date,
        carrier.carrier_code as carrier_code

      FROM
        (
          (
           SELECT
               cust_id,our_run_date,'C' as carrier_matchkey 
           FROM
               batch_insurance1_t
           WHERE
               emdeon_commercial_flag in ('1','2') 
          ) 
          UNION ALL
          (
           SELECT
               cust_id,our_run_date,state as carrier_matchkey 
           FROM
               batch_insurance1_t
           WHERE
               emdeon_medicaid_flag in ('1','2') 
          ) 
          UNION ALL
          (
           SELECT
               cust_id,our_run_date,'M' as carrier_matchkey 
           FROM
               batch_insurance1_t
           WHERE
               emdeon_medicare_flag in ('1','2')
          ) 

        ) as account

        JOIN

        ( SELECT 
             *
          FROM
             carrier_code_desc_t 
          WHERE
             carrier_audit_flag = 'Y'

         ) as carrier

         ON

        (account.carrier_matchkey = carrier.carrier_matchkey)

    ) as final

    GROUP BY
      cust_id,our_run_date,carrier_code

  ;



RAISE NOTICE 'DONE INSERT INTO insurance1_sent_detail_t TABLE : %', v_status;
--PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,8,'DONE INSERT IN insurance1_sent_detail_t');



  return v_status;
END;
$$ LANGUAGE plpgsql;
